package com.wlj.util;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

@Service
public class FileServiceImpl implements FileService {

    private static final Logger logger = LoggerFactory.getLogger(FileServiceImpl.class);
    @Override
    public String fileImport(String tableName,String tableComment,MultipartFile file) throws IOException {
        logger.info("表名:"+tableName+",表名注释:"+tableComment);
//        int start = tableName.indexOf("smyg_")+"smyg_".length();
        String uid = tableName+"_uuid";
        logger.info("uuid名称为,uid={}",uid);
        String result = "CREATE TABLE "+tableName+" (id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',\n" +
                ""+uid+" varchar(255) CHARACTER SET utf8 NOT NULL COMMENT '"+tableComment+"uuid',\n" +
                "create_time timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',\n" +
                "modify_time timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',\n";
        String bef = " NOT NULL COMMENT '";
        List<WebDto> list = new ArrayList<WebDto>();
        HSSFWorkbook book = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
        HSSFSheet sheet = book.getSheetAt(0);

        for(int i=1; i<sheet.getLastRowNum()+1; i++) {
            HSSFRow row = sheet.getRow(i);
            String type = " varchar(255) ";
            String name = row.getCell(0).getStringCellValue(); //名称
            try{

                if(!"".equals(row.getCell(1).getStringCellValue())){
                    type = row.getCell(1).getStringCellValue();
                }
            }catch(Exception e){

            }
            String remark = row.getCell(2).getStringCellValue(); //注释
            WebDto dto = new WebDto();
            dto.setName(name);
            dto.setType(type);
            dto.setRemark(remark);

            list.add(dto);
        }
        for(WebDto wd : list) {
            String res="";
            if(wd.getType().contains("timestamp") || wd.getType().contains("TIMESTAMP")){
                res = wd.getName() + " "+wd.getType() +" NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '"+ wd.getRemark()+"',\n";
            }else{
                res = wd.getName() + " "+wd.getType() +" DEFAULT NULL COMMENT '"+ wd.getRemark()+"',\n";
            }
            result += res;
        }
        result += "var1 varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '备用字段1',\n" +
                "var2 varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '备用字段2',\n" +
                "var3 varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '备用字段3',\n" +
                "PRIMARY KEY (`id`)\n" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='"+tableComment+"';";
        return result;
    }
}
